package dao.impl;

import bean.Area;
import bean.ClassB;
import dao.IAreaDao;
import util.DBUTil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AreaDaoImpl implements IAreaDao{
    @Override
    public List<Area> listByPage(int pageNo, int pageSize) {
        List<Area> areas =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn=DBUTil.getConn();
            //    SELECT
            //    t_area.pkid,
            //    t_area.f_areaName,
            //    t_area.f_forestType,
            //    t_area.f_landType,
            //    t_area.f_advantageTreeType
            //            FROM
            //    t_area
            String sql ="select ta.pkid as taId,f_areaName,f_forestType,f_landType,f_advantageTreeType," +
                    " tc.pkid as tcId, f_className, f_leader, f_tel" +
                    " from t_area as ta inner join t_class as tc where ta.pkid=tc.pkid limit ?,?";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs =ps.executeQuery();
            while(rs.next()){
                Area a =null;
                int taId = rs.getInt("taId");
                String f_areaName = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");
                a =new Area(taId,f_areaName,f_forestType,f_landType,f_advantageTreeType);
                ClassB cb =null;
                int tcId = rs.getInt("tcId");
//                where ta.pkid=tc.pkid

                String f_className = rs.getString("f_className");
                String f_leader = rs.getString("f_leader");
                String f_tel = rs.getString("f_tel");
                cb =new ClassB(tcId,f_className,f_leader,f_tel);
                a.setClassB(cb);
                areas.add(a);
            }
            List<Area> all = getAll();
//            System.out.println(all.size());
            List<Integer> nums =new ArrayList<>();

            for (int i = 0; i <all.size() ; i++) {
//                Area a =areas.get(i);
                if(i>areas.size()-1){
                    nums.add(i);
//                    System.out.println("i的值 = " + i);
                }
            }
            for(int i=0;i<nums.size();i++){
                areas.add(all.get(nums.get(i)));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }

        return areas;
    }



    @Override
    public int getCount() {
        int count =0;
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0) from t_area";
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }

    @Override
    public List<Area> listByPage(int pageNo, int pageSize, String f_areaName, String f_forestType, String f_className) {
        List<Area> areas =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn=DBUTil.getConn();
            //    SELECT
            //    t_area.pkid,
            //    t_area.f_areaName,
            //    t_area.f_forestType,
            //    t_area.f_landType,
            //    t_area.f_advantageTreeType
            //            FROM
            //    t_area
            String sql ="select ta.pkid as taId,f_areaName,f_forestType,f_landType,f_advantageTreeType," +
                    " tc.pkid as tcId, f_className, f_leader, f_tel" +
                    " from t_area as ta inner join t_class as tc where ta.pkid=tc.pkid ";
            if(f_areaName!=null){
                sql=sql+" and f_areaName like '%"+f_areaName+"%'";
            }
            if(f_forestType!=null){
                sql =sql+" and f_forestType like '%"+f_forestType+"%'";
            }
            if(f_className!=null){
                sql =sql+" and f_className like '%"+f_className+"%'";
            }
            sql+="  limit "+(pageNo-1)*pageSize+","+pageSize+"";
            ps =conn.prepareStatement(sql);

            rs =ps.executeQuery();
            while(rs.next()){
                Area a =null;
                int taId = rs.getInt("taId");
                String f_areaName1 = rs.getString("f_areaName");
                String f_forestType1 = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");
                a =new Area(taId,f_areaName1,f_forestType1,f_landType,f_advantageTreeType);
                ClassB cb =null;
                int tcId = rs.getInt("tcId");
                String f_className1 = rs.getString("f_className");
                String f_leader = rs.getString("f_leader");
                String f_tel = rs.getString("f_tel");
                cb =new ClassB(tcId,f_className1,f_leader,f_tel);
                a.setClassB(cb);
                areas.add(a);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }

        return areas;
    }

    @Override
    public int getCount(String f_areaName, String f_forestType, String f_className) {
        int count =0;
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0),f_className from t_area inner join t_class Where 1=1 and t_area.pkid =t_class.pkid ";
            if(f_areaName!=null&&!f_areaName.equals("")){
                sql=sql+" and f_areaName like '%"+f_areaName+"%'";
            }
            if(f_forestType!=null&&!f_forestType.equals("")){
                sql =sql+" and f_forestType like '%"+f_forestType+"%'";
            }
            if(f_className!=null&&!f_className.equals("")){
                sql =sql+" and f_className like '%"+f_className+"%'";
            }
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }

    @Override
    public int addOneArea(Area a) {
        int count =-1;
        Connection conn =null;
        PreparedStatement ps =null;

        try {
//            private String f_areaName;
//            private String f_forestType;
//            private String f_landType;
//            private String f_advantageTreeType;
            conn =DBUTil.getConn();
            String sql ="insert into t_area(f_areaName,f_forestType,f_landType,f_advantageTreeType) values(?,?,?,?)";
            ps =conn.prepareStatement(sql);
            ps.setString(1,a.getF_areaName());
            ps.setString(2,a.getF_forestType());
            ps.setString(3,a.getF_landType());
            ps.setString(4,a.getF_advantageTreeType());
            count =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(null,ps,conn);
        }


        return count;
    }

    @Override
    public List<Area> getAll() {

        List<Area> areas =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn=DBUTil.getConn();
            //    SELECT
            //    t_area.pkid,
            //    t_area.f_areaName,
            //    t_area.f_forestType,
            //    t_area.f_landType,
            //    t_area.f_advantageTreeType
            //            FROM
            //    t_area
            String sql ="select * from t_area ";
            ps =conn.prepareStatement(sql);

            rs =ps.executeQuery();
            while(rs.next()){
                Area a =null;
                int taId = rs.getInt("pkid");
                String f_areaName = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");
                a =new Area(taId,f_areaName,f_forestType,f_landType,f_advantageTreeType);
                areas.add(a);

            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }

        return areas;
    }

    @Override
    public List<Area> getNoClassArea() {
//        List<Area> all = getAll();
        List<Area> areas =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn =DBUTil.getConn();
            String sql ="select * from t_area  where not exists(select t_class.pkid from t_class where t_class.pkid=t_area.pkid)";
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            while(rs.next()){
                Area a =null;
                int taId = rs.getInt("pkid");
                String f_areaName = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");
                a =new Area(taId,f_areaName,f_forestType,f_landType,f_advantageTreeType);
                areas.add(a);
            }
//            //            System.out.println(all.size());
//            List<Integer> nums =new ArrayList<>();
//
//            for (int i = 0; i <all.size() ; i++) {
////                Area a =areas.get(i);
//                if(i<=areas.size()-1){
//                    nums.add(i);
////                    System.out.println("i的值 = " + i);
//                }
//            }
//            for(int i=0;i<nums.size();i++){
//                all.remove(nums.get(i));
//            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }


        return areas;
    }

    @Override
    public Area findById(int id) {

        Area a =null;
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn =DBUTil.getConn();
            String sql ="select * from t_area where pkid =?";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs =ps.executeQuery();
            if(rs.next()){

                int taId = rs.getInt("pkid");
                String f_areaName = rs.getString("f_areaName");
                String f_forestType = rs.getString("f_forestType");
                String f_landType = rs.getString("f_landType");
                String f_advantageTreeType = rs.getString("f_advantageTreeType");
                a =new Area(taId,f_areaName,f_forestType,f_landType,f_advantageTreeType);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }


        return a;
    }
}
